import 'package:mysql1/mysql1.dart';
import '../models/acceptance_category.dart';
import '../models/acceptance_form.dart';
import 'dart:convert';

class AcceptanceService {
  static final AcceptanceService _instance = AcceptanceService._internal();
  factory AcceptanceService() => _instance;
  AcceptanceService._internal();

  List<AcceptanceCategory> categories = [];
  MySqlConnection? _connection;

  Future<MySqlConnection> _getConnection() async {
    if (_connection != null) {
      try {
        // 测试连接是否仍然有效
        await _connection!.query('SELECT 1');
        return _connection!;
      } catch (e) {
        // 如果连接无效，关闭并重新创建
        await _connection!.close();
        _connection = null;
      }
    }

    final settings = ConnectionSettings(
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: 'root',
      db: 'accept',
    );

    print('正在连接数据库...');
    print('连接参数: host=${settings.host}, port=${settings.port}, db=${settings.db}, user=${settings.user}');
    
    try {
      _connection = await MySqlConnection.connect(settings);
      // 设置字符集
      await _connection!.query('SET NAMES utf8mb4');
      await _connection!.query('SET CHARACTER SET utf8mb4');
      await _connection!.query('SET character_set_connection=utf8mb4');
      await _connection!.query('SET character_set_client=utf8mb4');
      await _connection!.query('SET character_set_results=utf8mb4');
      print('数据库连接成功');
      return _connection!;
    } catch (e) {
      print('数据库连接失败: $e');
      rethrow;
    }
  }

  Future<void> closeConnection() async {
    if (_connection != null) {
      await _connection!.close();
      _connection = null;
    }
  }

  Future<List<AcceptanceCategory>> getCategories() async {
    if (categories.isNotEmpty) {
      return categories;
    }

    final conn = await _getConnection();
    try {
      final results = await conn.query(
        'SELECT * FROM acceptance_categories ORDER BY level, sort_order',
      );

      categories = results.map((row) {
        return AcceptanceCategory(
          id: row['id'],
          parentId: row['parent_id'],
          name: row['name'],
          level: row['level'],
          sortOrder: row['sort_order'],
        );
      }).toList();

      return categories;
    } catch (e) {
      print('获取分类失败: $e');
      rethrow;
    }
  }

  Future<String> generateFormNumber() async {
    final conn = await _getConnection();
    final today = DateTime.now();
    final dateStr = '${today.year}${today.month.toString().padLeft(2, '0')}${today.day.toString().padLeft(2, '0')}';
    
    try {
      final results = await conn.query(
        'SELECT MAX(form_number) as max_number FROM acceptance_forms WHERE form_number LIKE ?',
        ['LPXMDJF$dateStr%'],
      );

      String number = '001';
      if (results.isNotEmpty) {
        final maxNumber = results.first['max_number'] as String?;
        if (maxNumber != null) {
          final currentNumber = int.parse(maxNumber.substring(15)) + 1;
          number = currentNumber.toString().padLeft(3, '0');
        }
      }

      return 'LPXMDJF$dateStr$number';
    } catch (e) {
      print('生成受理单编号失败: $e');
      rethrow;
    }
  }

  Future<void> createAcceptanceForm(AcceptanceForm form) async {
    final conn = await _getConnection();
    
    try {
      // 生成受理单编号
      form.formNumber = await generateFormNumber();
      
      // 将日期转换为 UTC 格式
      final utcDate = form.acceptanceDate.toUtc();
      
      await conn.query(
        'INSERT INTO acceptance_forms ('
        'form_number, acceptance_date, applicant_name, applicant_phone, '
        'applicant_id_card, respondent_name, respondent_phone, respondent_id_card, '
        'dispute_description, acceptor_name, acceptor_unit, acceptor_phone, '
        'category_id, status, created_by'
        ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
        [
          form.formNumber,
          utcDate,
          form.applicantName,
          form.applicantPhone,
          form.applicantIdCard,
          form.respondentName,
          form.respondentPhone,
          form.respondentIdCard,
          form.disputeDescription,
          form.acceptorName,
          form.acceptorUnit,
          form.acceptorPhone,
          form.categoryId,
          form.status,
          1, // 暂时hardcode created_by为1
        ],
      );
    } catch (e) {
      print('创建受理单失败: $e');
      rethrow;
    }
  }

  // 获取所有受理单
  Future<List<AcceptanceForm>> getAcceptanceForms() async {
    final conn = await _getConnection();
    try {
      final results = await conn.query(
        'SELECT * FROM acceptance_forms ORDER BY acceptance_date DESC',
      );

      return results.map((row) {
        try {
          return AcceptanceForm(
            formNumber: _convertToString(row['form_number']),
            acceptanceDate: _convertToDateTime(row['acceptance_date']).toLocal(),
            applicantName: _convertToString(row['applicant_name']),
            applicantPhone: _convertToString(row['applicant_phone']),
            applicantIdCard: _convertToString(row['applicant_id_card']),
            respondentName: _convertToString(row['respondent_name']),
            respondentPhone: _convertToString(row['respondent_phone']),
            respondentIdCard: _convertToString(row['respondent_id_card']),
            disputeDescription: _convertToString(row['dispute_description']),
            acceptorName: _convertToString(row['acceptor_name']),
            acceptorUnit: _convertToString(row['acceptor_unit']),
            acceptorPhone: _convertToString(row['acceptor_phone']),
            categoryId: _convertToInt(row['category_id']),
            status: _convertToInt(row['status']),
          );
        } catch (e) {
          print('转换数据失败: $e');
          print('行数据: $row');
          rethrow;
        }
      }).toList();
    } catch (e) {
      print('获取受理单列表失败: $e');
      rethrow;
    }
  }

  // 根据受理单号获取受理单
  Future<AcceptanceForm> getAcceptanceFormByNumber(String formNumber) async {
    final conn = await _getConnection();
    try {
      final results = await conn.query(
        'SELECT * FROM acceptance_forms WHERE form_number = ?',
        [formNumber],
      );

      if (results.isEmpty) {
        throw Exception('未找到受理单: $formNumber');
      }

      final row = results.first;
      return AcceptanceForm(
        formNumber: _convertToString(row['form_number']),
        acceptanceDate: _convertToDateTime(row['acceptance_date']).toLocal(),
        applicantName: _convertToString(row['applicant_name']),
        applicantPhone: _convertToString(row['applicant_phone']),
        applicantIdCard: _convertToString(row['applicant_id_card']),
        respondentName: _convertToString(row['respondent_name']),
        respondentPhone: _convertToString(row['respondent_phone']),
        respondentIdCard: _convertToString(row['respondent_id_card']),
        disputeDescription: _convertToString(row['dispute_description']),
        acceptorName: _convertToString(row['acceptor_name']),
        acceptorUnit: _convertToString(row['acceptor_unit']),
        acceptorPhone: _convertToString(row['acceptor_phone']),
        categoryId: _convertToInt(row['category_id']),
        status: _convertToInt(row['status']),
      );
    } catch (e) {
      print('获取受理单失败: $e');
      rethrow;
    }
  }

  // 更新受理单
  Future<void> updateAcceptanceForm(AcceptanceForm form) async {
    final conn = await _getConnection();
    try {
      await conn.query(
        '''
        UPDATE acceptance_forms 
        SET acceptance_date = ?,
            applicant_name = ?,
            applicant_phone = ?,
            applicant_id_card = ?,
            respondent_name = ?,
            respondent_phone = ?,
            respondent_id_card = ?,
            dispute_description = ?,
            acceptor_name = ?,
            acceptor_unit = ?,
            acceptor_phone = ?,
            category_id = ?,
            status = ?
        WHERE form_number = ?
        ''',
        [
          form.acceptanceDate.toUtc(),
          form.applicantName,
          form.applicantPhone,
          form.applicantIdCard,
          form.respondentName,
          form.respondentPhone,
          form.respondentIdCard,
          form.disputeDescription,
          form.acceptorName,
          form.acceptorUnit,
          form.acceptorPhone,
          form.categoryId,
          form.status,
          form.formNumber,
        ],
      );
    } catch (e) {
      print('更新受理单失败: $e');
      rethrow;
    }
  }

  // 删除受理单
  Future<void> deleteAcceptanceForm(String formNumber) async {
    final conn = await _getConnection();
    try {
      await conn.query(
        'DELETE FROM acceptance_forms WHERE form_number = ?',
        [formNumber],
      );
    } catch (e) {
      print('删除受理单失败: $e');
      rethrow;
    }
  }

  String _convertToString(dynamic value) {
    if (value == null) return '';
    if (value is String) {
      print('字符串值: $value');
      return value;
    }
    if (value is Blob) {
      final bytes = value.toBytes();
      print('Blob 数据长度: ${bytes.length}');
      print('Blob 数据: $bytes');
      
      // 尝试使用 UTF-8 解码
      try {
        final result = utf8.decode(bytes, allowMalformed: true);
        print('UTF-8 解码结果: $result');
        return result;
      } catch (e) {
        print('UTF-8 解码失败: $e');
        // 如果 UTF-8 解码失败，尝试使用 Latin1 解码
        try {
          final result = String.fromCharCodes(bytes);
          print('Latin1 解码结果: $result');
          return result;
        } catch (e) {
          print('Latin1 解码失败: $e');
          return '解码失败';
        }
      }
    }
    return value.toString();
  }

  DateTime _convertToDateTime(dynamic value) {
    if (value == null) return DateTime.now();
    if (value is DateTime) return value;
    if (value is String) return DateTime.parse(value);
    throw Exception('无法转换为DateTime: $value');
  }

  int _convertToInt(dynamic value) {
    if (value == null) return 0;
    if (value is int) return value;
    if (value is String) return int.tryParse(value) ?? 0;
    if (value is double) return value.toInt();
    return 0;
  }
} 